﻿
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports System.Data
Imports System.Drawing
Imports NPOI
Imports NPOI.HPSF
Imports NPOI.HSSF
Imports NPOI.HSSF.UserModel
Imports NPOI.POIFS
Imports NPOI.Util

Partial Class WebForm_ManageWorkRecord_ImportWorkRecord
    Inherits MyUserControl


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnExport2)
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnImport)
        If Not IsPostBack Then
            VolunteerQueryControl1.IsVolunteerLeader = IIf(Me.CanAdmin = False And Me.CanQuery = True, True, False)
            VolunteerQueryControl1.InitData()
        End If
        '只有管理權限才可以不要檢查紀錄冊號碼
        If Me.CanAdmin = False Then
            Me.cbnotVBookID.Visible = False
        Else
            Me.cbNotReserve.Visible = True
        End If

        '只有管理或編輯權限才可以匯入時數
        If Me.CanAdmin = True OrElse Me.CanEdit = True Then
            Me.plImport.Visible = True
        Else
            Me.plImport.Visible = False
        End If
    End Sub
    'Protected Sub ddlOrganization_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

    '    If ddlOrganization.Text <> "" Then

    '        Me.phVOrganizationID.Visible = True
    '        UOrganizationID = ddlOrganization.Text

    '        RaiseEvent OrgIndexChange(Me, New EventArgs)
    '    Else
    '        Me.phVOrganizationID.Visible = False
    '    End If

    'End Sub
    Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click

        If Me.SaveData() Then
            MyObj.ShowMessage("匯入成功！")
        End If

    End Sub

    Protected Sub btnExport2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport2.Click
        'If Me.ddlOrganization.Text <> "" Then
        Me.Export2()
        'Else
        '    MyObj.ShowMessage("請選擇運用單位!")
        'End If
    End Sub

    'Protected Sub btnImport_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.PreRender
    '    ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(sender)
    'End Sub

    Private Function GetGVData() As Data.DataTable
        Dim dao As New BLL_ImportVolunteer_SearchRegionVolunteerControl
        Return dao.BLL_ImportRecord(Me.VolunteerQueryControl1.OrganizationID, Me.VolunteerQueryControl1.VOrganizationID, Me.VolunteerQueryControl1.VolunteerId, Me.VolunteerQueryControl1.VolunteerName)

        'Dim VID As Integer = Int32.Parse(IIf(Me.ddlVOrganizationID.Text <> "", Me.ddlVOrganizationID.Text, 0))
        'Return dao.BLL_ImportRecord(Me.ddlOrganization.Text, VID, Me.VolunteerQueryControl1.VolunteerId, Me.VolunteerQueryControl1.VolunteerName)
    End Function


    Private Function SaveData() As Boolean

        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("請選擇上傳的檔案")
            Return False
        ElseIf FileUpload1.PostedFile.ContentType.ToString() <> "application/vnd.ms-excel" AndAlso FileUpload1.PostedFile.ContentType.ToString() <> "application/octet-stream" Then
            MyObj.ShowMessage("匯入失敗!!請選擇副檔名(xls、csv)檔案!!")
            Return False
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New DataTable

        '檢查工作表名稱
        Try
            If ExcelImportCheck.LoadData(dt, newName, "ImportWorkRecordSample") = False Then
                Me.gv.Visible = False
                Return False
            End If
        Catch ex As Exception
            MyObj.ShowMessage("資料格式異常！")
            Return False
        End Try


        '檢查欄位是否都存在
        Dim ColumnList As String(,) = {{"姓名", "N"}, {"身份證號碼", "N"}, {"紀錄冊號碼", "N"}, {"年度月份(起)", "N"}, {"年度月份(迄)", "N"}, {"總時數", "N"}, {"環境清掃", "N"}, {"宣導", "N"}, {"導覽", "N"}, {"河川巡守", "N"}}
        If ExcelImportCheck.CheckColumn(dt, ColumnList) = False Then
            Me.gv.Visible = False
            Return False
        End If

        '隱藏錯誤訊息GridView
        Me.gv.Visible = False
        For j As Integer = 0 To dt.Columns.Count - 1
           
            dt.Columns(j).ColumnName = dt.Columns(j).ColumnName.Trim()
         
        Next

        '濾掉空白資料
        Dim FilterValue As String = ""
        For k As Integer = 0 To (ColumnList.Length / 2) - 1
            If FilterValue = "" Then
                FilterValue &= "[" & ColumnList(k, 0) & "]<>''"
            Else
                FilterValue &= " or [" & ColumnList(k, 0) & "]<>''"
            End If
        Next
        Dim MyDefaultView As Data.DataView = dt.DefaultView
        MyDefaultView.RowFilter = FilterValue
        dt = MyDefaultView.ToTable()

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        '取出志工資料
        '==============================================
        sql.Clear()
        Dim VolunteerDT, VolunteerTempDT As Data.DataTable
        Dim VolunteerTempView As DataView

        sql.AppendFormat("Select replace(replace(left(VolunteerID,3)+REPLICATE('X',3)+right(VolunteerID,4)+VolunteerName, '  ', ''), ' ', '')  As VolunteerData from Volunteer; ")
        VolunteerDT = dat.GetData(sql)
        '==============================================


        '取出志工証資料
        '==============================================
        sql.Clear()
        Dim VBookDT, VBookTempDT As Data.DataTable
        Dim VBookTempView As DataView

        sql.AppendFormat(" Select VolunteerID + '-' + SerialGroupID + '-' + SerialNumber As VBookData From Vbook Where Active='Y'; ")
        VBookDT = dat.GetData(sql)
        '==============================================


        '取出系統設定的時數上限
        '==============================================
        'sql.Clear()

        'sql.AppendFormat(" Select ParameterValue from SystemParameter ")
        'sql.AppendFormat(" Where ParameterType='HonorCert' And ParameterName='ApplyWorkHour' ")
        'Dim HonorCertHour As Integer = CType(dat.GetData(sql).Rows(0)(0).ToString(), Integer)

        '==============================================



   

        '開始逐步檢查
     
        Dim i As Integer
        Dim ErrorCount As Integer = 0

        Dim colString As DataColumn = New DataColumn("檢查結果")
        colString.DataType = System.Type.GetType("System.String")
        dt.Columns.Add(colString)
        colString = New DataColumn("年度起")
        colString.DataType = System.Type.GetType("System.String")
        dt.Columns.Add(colString)
        colString = New DataColumn("年度迄")
        colString.DataType = System.Type.GetType("System.String")
        dt.Columns.Add(colString)
        For i = 0 To dt.Rows.Count - 1
            Dim inYYMM As String = ""
            Dim inYYMMEnd As String = ""

            '檢查志工姓名跟身份證號碼核對
            If dt.Rows(i)("姓名").ToString().Trim() = "" OrElse dt.Rows(i)("身份證號碼").ToString().Trim() = "" Then
                dt.Rows(i)("檢查結果") &= "姓名及身分證請填寫完整,"
                ErrorCount = ErrorCount + 1
            Else
                VolunteerTempView = VolunteerDT.DefaultView
                dt.Rows(i)("身份證號碼") = Left(dt.Rows(i)("身份證號碼").ToString().Trim(), 3) & New String("X", 3) & Right(dt.Rows(i)("身份證號碼").ToString().Trim(), 4)
                Dim VString As String = dt.Rows(i)("身份證號碼").ToString() & LTrim(dt.Rows(i)("姓名").ToString().Trim().Replace("  ", "").Replace(" ", ""))
                VolunteerTempView.RowFilter = "VolunteerData='" + VString.Replace("  ", "").Replace(" ", "") + "'"
                '    MyObj.WriteErrorLog(VString.Replace("  ", "").Replace(" ", ""))
                VolunteerTempDT = VolunteerTempView.ToTable()

                If VolunteerTempDT.Rows.Count = 0 Then
                    dt.Rows(i)("檢查結果") &= "身份證號碼核對錯誤,"
                    ErrorCount = ErrorCount + 1
                Else

                    If GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim()) <> "" AndAlso GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim()) <> "Error" Then
                        dt.Rows(i)("身份證號碼") = GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim())


                    ElseIf GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim()) = "Error" Then
                        dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "的身分證字號大於兩筆,"
                        ErrorCount = ErrorCount + 1
                    Else
                        dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "的身分證字號無法比對,"
                        ErrorCount = ErrorCount + 1
                    End If
                End If
            End If


            '檢查生日格式 72.07.02 '個資法不帶生日欄位 mask by Shihyu 2011/08/19
            'If dt.Rows(i)("生日").ToString().Trim() = "" Then
            '    dt.Rows(i)("檢查結果") &= "生日未填(格式YYY.MM.DD/YY.MM.DD),)"
            '    ErrorCount = ErrorCount + 1
            'Else
            '    If dt.Rows(i)("生日").ToString().Trim().Length < 8 OrElse dt.Rows(i)("生日").ToString().Trim().Length > 9 Then
            '        dt.Rows(i)("檢查結果") &= "生日長度有誤(格式YYY.MM.DD/YY.MM.DD),"
            '        ErrorCount = ErrorCount + 1
            '    Else
            '        If Not dt.Rows(i)("生日").ToString().Trim().Contains(".") Then
            '            dt.Rows(i)("檢查結果") &= "生日格式--- 無(.) (格式YYY.MM.DD/YY.MM.DD),"
            '            ErrorCount = ErrorCount + 1
            '        End If

            '        Dim birthday() As String = dt.Rows(i)("生日").ToString().Trim().Split(".")

            '        If birthday.Length <> 3 Then
            '            dt.Rows(i)("檢查結果") &= "生日格式錯誤(格式YYY.MM.DD/YY.MM.DD),"
            '            ErrorCount = ErrorCount + 1
            '        End If

            '        For m As Integer = 0 To birthday.Length - 1
            '            If birthday(m).Length <> 2 Then
            '                dt.Rows(i)("檢查結果") &= "生日格式錯誤--月(格式YYY.MM.DD/YY.MM.DD),"
            '                ErrorCount = ErrorCount + 1
            '            End If
            '        Next

            '    End If
            'End If


            '檢查年度月份(起)格式 99.06 or YY.MM/YYY.MM  ---- 民國百年
            Dim inYY As String = ""
            If dt.Rows(i)("年度月份(起)").ToString().Trim() = "" OrElse TypeOf (dt.Rows(i)("年度月份(起)")) Is DBNull Then
                dt.Rows(i)("檢查結果") &= "年度月份(起)未填(格式YYY.MM/YY.MM，年份爲民國年),"
                ErrorCount = ErrorCount + 1
            Else
                If dt.Rows(i)("年度月份(起)").ToString().Trim().Length < 5 OrElse dt.Rows(i)("年度月份(起)").ToString().Trim().Length > 6 Then
                    dt.Rows(i)("檢查結果") &= "年度月份(起)長度錯誤(格式YYY.MM/YY.MM，年份爲民國年),"
                    ErrorCount = ErrorCount + 1
                ElseIf dt.Rows(i)("年度月份(起)").ToString().Trim().Contains(".") = False Then
                    dt.Rows(i)("檢查結果") &= "年度月份(起)格式錯誤--- 無(.) (格式YYY.MM/YY.MM，年份爲民國年),"
                    ErrorCount = ErrorCount + 1
                Else



                    Dim yymm() As String = dt.Rows(i)("年度月份(起)").ToString().Trim().Split(".")



                    '年
                    If yymm(0).Length > 3 OrElse yymm(0).Length <= 0 Then
                        dt.Rows(i)("檢查結果") &= "年度月份(起)的年份長度有誤(格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    ElseIf IsNumeric(yymm(0)) = False OrElse Int32.Parse(yymm(0)) < 0 OrElse Left(yymm(0), 1) = "0" Then
                        dt.Rows(i)("檢查結果") &= "年度月份(起)的年份不可爲非數值或負值 (格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    Else
                        inYY = (CType(yymm(0), Integer) + 1911).ToString()
                    End If

                    '月
                    If yymm(1).Length > 2 OrElse yymm(1).Length <= 1 Then
                        dt.Rows(i)("檢查結果") &= "年度月份(起)的月份長度有誤(格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    ElseIf IsNumeric(yymm(1)) = False OrElse Int32.Parse(yymm(1)) < 0 Then
                        dt.Rows(i)("檢查結果") &= "年度月份(起)的月份不可爲非數值或負值(格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    ElseIf MonthString.Contains(yymm(1)) = False Then
                        dt.Rows(i)("檢查結果") &= String.Format("年度月份(起)的月份值有誤(月份的正常值爲{0}),", MonthString.Replace(",", "/"))
                        ErrorCount = ErrorCount + 1
                    ElseIf inYY <> "" Then
                        '匯入的年度月份(起)

                        inYYMM = inYY + "/" + yymm(1) + "/01"
                        dt.Rows(i)("年度起") = inYYMM
                    End If
                End If



            End If


            '檢查年度月份(迄)格式 99.06  or YY.MM/YYY.MM  ---- 民國百年
            Dim inYYEnd As String = ""
            If dt.Rows(i)("年度月份(迄)").ToString().Trim() = "" OrElse TypeOf (dt.Rows(i)("年度月份(迄)")) Is DBNull Then
                dt.Rows(i)("檢查結果") &= "年度月份(迄)未填(格式YYY.MM/YY.MM，年份爲民國年),"
                ErrorCount = ErrorCount + 1
            Else
                If dt.Rows(i)("年度月份(迄)").ToString().Trim().Length < 5 OrElse dt.Rows(i)("年度月份(迄)").ToString().Trim().Length > 6 Then
                    dt.Rows(i)("檢查結果") &= "年度月份(迄)長度錯誤(格式YYY.MM/YY.MM，年份爲民國年),"
                    ErrorCount = ErrorCount + 1
                ElseIf dt.Rows(i)("年度月份(迄)").ToString().Trim().Contains(".") = False Then
                    dt.Rows(i)("檢查結果") &= "年度月份(迄)格式錯誤--- 無(.) (格式YYY.MM/YY.MM，年份爲民國年),"
                Else

                    Dim yymm2() As String = dt.Rows(i)("年度月份(迄)").ToString().Trim().Split(".")


                    '年
                    If yymm2(0).Length <= 0 OrElse yymm2(0).Length > 3 Then
                        dt.Rows(i)("檢查結果") &= "年度月份(迄)的年份長度有誤(格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    ElseIf IsNumeric(yymm2(0).ToString()) = False OrElse Int32.Parse(yymm2(0)) < 0 OrElse Left(yymm2(0), 1) = "0" Then
                        dt.Rows(i)("檢查結果") &= "年度月份(迄)的年份不可爲非數值或負值 (格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    Else
                        inYYEnd = (CType(yymm2(0), Integer) + 1911).ToString()
                    End If

                    '月
                    If yymm2(1).Length > 2 OrElse yymm2(1).Length <= 1 Then
                        dt.Rows(i)("檢查結果") &= "年度月份(迄)的月份長度有誤(格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    ElseIf IsNumeric(yymm2(1)) = False OrElse Int32.Parse(yymm2(1)) < 0 Then
                        dt.Rows(i)("檢查結果") &= "年度月份(迄)的月份不可爲非數值或負值 (格式YYY.MM/YY.MM，年份爲民國年),"
                        ErrorCount = ErrorCount + 1
                    ElseIf MonthString.Contains(yymm2(1)) = False Then
                        dt.Rows(i)("檢查結果") &= String.Format("年度月份(迄)的月份值有誤(月份的正常值爲{0}),", MonthString.Replace(",", "/"))
                        ErrorCount = ErrorCount + 1
                    ElseIf inYYEnd <> "" Then
                        '匯入的年度月份(迄)
                        inYYMMEnd = inYYEnd + "/" + yymm2(1) + "/01"
                        dt.Rows(i)("年度迄") = inYYMMEnd
                    End If

                End If
            End If

            Dim NowDate As String = Now.Year & "/" & Now.Month & "/01"
      
            '年度月份(起)不可大於年度月份(迄)/年度月份(迄)不可大於當年當月
            If inYYMM <> "" AndAlso inYYMMEnd <> "" Then
                If CType(inYYMMEnd, DateTime) > CType(NowDate, DateTime) Then
     
                    dt.Rows(i)("檢查結果") &= "年度月份(迄)不可大於當年當月,"
                    ErrorCount = ErrorCount + 1
                End If
                If CType(inYYMM, DateTime) > CType(inYYMMEnd, DateTime) Then
                    dt.Rows(i)("檢查結果") &= "年度月份(起)不可大於年度月份(迄),"
                    ErrorCount = ErrorCount + 1
                End If
            End If


            '檢查總時數
            Dim HourErrorCount As Integer = 0
            If dt.Rows(i)("總時數").ToString().Trim() = "" Then
                dt.Rows(i)("檢查結果") &= "總時數未填,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            ElseIf IsNumeric(dt.Rows(i)("總時數").ToString().Trim()) = False OrElse Double.Parse(dt.Rows(i)("總時數").ToString().Trim()) < 0 Then
                dt.Rows(i)("檢查結果") &= "總時數不可爲非數值或負數,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            End If

            '檢查環境清掃
            If dt.Rows(i)("環境清掃").ToString().Trim() = "" Then
                dt.Rows(i)("檢查結果") &= "環境清掃時數未填,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            ElseIf IsNumeric(dt.Rows(i)("環境清掃").ToString().Trim()) = False OrElse Double.Parse(dt.Rows(i)("環境清掃").ToString().Trim()) < 0 Then
                dt.Rows(i)("檢查結果") &= "環境清掃不可爲非數值或負數,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            End If

            '檢查宣導
            If dt.Rows(i)("宣導").ToString().Trim() = "" Then
                dt.Rows(i)("檢查結果") &= "宣導時數未填,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            ElseIf IsNumeric(dt.Rows(i)("宣導").ToString().Trim()) = False OrElse Double.Parse(dt.Rows(i)("宣導").ToString().Trim()) < 0 Then
                dt.Rows(i)("檢查結果") &= "宣導不可爲非數值或負數,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            End If

            '檢查導覽
            If dt.Rows(i)("導覽").ToString().Trim() = "" Then
                dt.Rows(i)("檢查結果") &= "導覽時數未填,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            ElseIf IsNumeric(dt.Rows(i)("導覽").ToString().Trim()) = False OrElse Double.Parse(dt.Rows(i)("導覽").ToString().Trim()) < 0 Then
                dt.Rows(i)("檢查結果") &= "導覽不可爲非數值或負數,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            End If

            '檢查河川巡守
            If dt.Rows(i)("河川巡守").ToString().Trim() = "" Then
                dt.Rows(i)("檢查結果") &= "河川巡守時數未填,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            ElseIf IsNumeric(dt.Rows(i)("河川巡守").ToString().Trim()) = False OrElse Double.Parse(dt.Rows(i)("河川巡守").ToString().Trim()) < 0 Then
                dt.Rows(i)("檢查結果") &= "河川巡守不可爲非數值或負數,"
                ErrorCount = ErrorCount + 1
                HourErrorCount = HourErrorCount + 1
            End If


            '檢查志工証
            If Not Me.cbnotVBookID.Checked Then
                If dt.Rows(i)("紀錄冊號碼").ToString().Trim() = "" Or Not (dt.Rows(i)("紀錄冊號碼").ToString().Contains("新北市字第") Or dt.Rows(i)("紀錄冊號碼").ToString().Contains("北縣字第")) Or Not dt.Rows(i)("紀錄冊號碼").ToString().Contains("號") Then
                    dt.Rows(i)("檢查結果") &= "紀錄冊號碼請填寫完整,"
                    ErrorCount = ErrorCount + 1
                Else
                    Dim txtNumber As String = dt.Rows(i)("紀錄冊號碼").ToString().Trim()

                    '取出紀錄冊類別
                    Dim serialGroupName As String = txtNumber.Substring(0, 1)

                    sql.Length = 0
                    sql.AppendFormat(" Select Value From Codemapping where tableName='VBook' And ColumnName = 'SerialGroupID' and Text = '{0}'; ", serialGroupName.Trim())

                    Dim serialGroupID As String = ""
                    If dat.GetData(sql).Rows.Count > 0 Then
                        serialGroupID = dat.GetData(sql).Rows(0)(0).ToString()
                    End If


                    '取出紀錄冊號碼
                    Dim serialNumber As String = txtNumber.Substring(txtNumber.IndexOf("第") + 1, txtNumber.Length - txtNumber.IndexOf("第") - 2)

                    VBookTempView = VBookDT.DefaultView
                    VBookTempView.RowFilter = "VBookData='" _
                                                & dt.Rows(i)("身份證號碼").ToString().Trim() _
                                                & "-" _
                                                & serialGroupID _
                                                & "-" _
                                                & serialNumber & "'"
                
                    VBookTempDT = VBookTempView.ToTable()

                    If VBookTempDT.Rows.Count = 0 Then
                        dt.Rows(i)("檢查結果") &= "紀錄冊號碼錯誤,"
                        ErrorCount = ErrorCount + 1
                    End If
                End If
            End If





            '檢查服務總時數---每月 估 85小時  ,  估 Max <  85 x 申報月份數 ( 迄 - 起)
            Dim AllHour As Double
            If inYYMMEnd <> "" AndAlso inYYMM <> "" AndAlso HourErrorCount = 0 Then

                If inYYEnd = inYY Then
                    AllHour = (Double.Parse(IIf(inYYMMEnd.Substring(5, 1) = "0", inYYMMEnd.Substring(6, 1), inYYMMEnd.Substring(5, 2))) - Double.Parse(IIf(inYYMM.Substring(5, 1) = "0", inYYMM.Substring(6, 1), inYYMM.Substring(5, 2))) + 1) * 85
                Else
                    AllHour = ((12 - Double.Parse(IIf(inYYMM.Substring(5, 1) = "0", inYYMM.Substring(6, 1), inYYMM.Substring(5, 2))) + 1) + 12 * (inYYEnd - inYY - 1) + Double.Parse(IIf(inYYMMEnd.Substring(5, 1) = "0", inYYMMEnd.Substring(6, 1), inYYMMEnd.Substring(5, 2)))) * 85
                End If
                '   MyObj.WriteErrorLog(dt.Rows(i)("總時數").ToString().Trim())
                If Double.Parse(dt.Rows(i)("總時數").ToString().Trim()) > AllHour Then
                    dt.Rows(i)("檢查結果") &= "服務總時數不可超過" + AllHour.ToString() & ","
                    ErrorCount = ErrorCount + 1
                End If



                '檢查各項目時數加總 = 服務總時數
                Dim hourCount1 As Double = Double.Parse(dt.Rows(i)("環境清掃").ToString().Trim())
                Dim hourCount2 As Double = Double.Parse(dt.Rows(i)("宣導").ToString().Trim())
                Dim hourCount3 As Double = Double.Parse(dt.Rows(i)("導覽").ToString().Trim())
                Dim hourCount4 As Double = Double.Parse(dt.Rows(i)("河川巡守").ToString().Trim())
                Dim totalCount As Double = Double.Parse(dt.Rows(i)("總時數").ToString().Trim())

                If Not totalCount = hourCount1 + hourCount2 + hourCount3 + hourCount4 Then
                    dt.Rows(i)("檢查結果") &= "總時數加總有誤,"
                    ErrorCount = ErrorCount + 1
                End If

            End If
            '若匯入時數包含認養時數,則須比對其大小


            'If Me.cbNotWorkHour.Checked = False Then

            'If Me.WorkHourRecord("1", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()) = "" Then
            '    dt.Rows(i)("環境清掃") = hourCount1
            'ElseIf hourCount1 < Int32.Parse(Me.WorkHourRecord("1", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim())) Then
            '    dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "---環境清掃時數須大於認養時數--,"
            '    ErrorCount = ErrorCount + 1
            'Else
            '    dt.Rows(i)("環境清掃") = hourCount1 - Int32.Parse(Me.WorkHourRecord("1", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()))
            'End If

            'If Me.WorkHourRecord("2", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()) = "" Then
            '    dt.Rows(i)("宣導") = hourCount2
            'ElseIf hourCount2 < Int32.Parse(Me.WorkHourRecord("2", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim())) Then
            '    dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "---宣導時數須大於認養時數--,"
            '    ErrorCount = ErrorCount + 1
            'Else
            '    dt.Rows(i)("宣導") = hourCount2 - Int32.Parse(Me.WorkHourRecord("2", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()))
            'End If

            'If Me.WorkHourRecord("3", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()) = "" Then
            '    dt.Rows(i)("導覽") = hourCount3
            'ElseIf hourCount3 < Int32.Parse(Me.WorkHourRecord("3", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim())) Then
            '    dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "---導覽時數須大於認養時數--,"
            '    ErrorCount = ErrorCount + 1
            'Else
            '    dt.Rows(i)("導覽") = hourCount3 - Int32.Parse(Me.WorkHourRecord("3", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()))
            'End If

            'If Me.WorkHourRecord("4", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()) = "" Then
            '    dt.Rows(i)("河川巡守") = hourCount4
            'ElseIf hourCount4 < Int32.Parse(Me.WorkHourRecord("4", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim())) Then
            '    dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "---河川巡守時數須大於認養時數--,"
            '    ErrorCount = ErrorCount + 1
            'Else
            '    dt.Rows(i)("河川巡守") = hourCount4 - Int32.Parse(Me.WorkHourRecord("4", inYYMM, inYYMMEnd, dt.Rows(i)("身份證號碼").ToString().Trim()))
            'End If


            'End If
        Next



        '-------------------若以上無錯誤，開始匯入資料-------------------------------------
        If ErrorCount > 0 Then
            MyObj.ShowMessage("匯入失敗！")
            Me.gv.Visible = True
            dt.Columns.Remove("年度起")
            dt.Columns.Remove("年度迄")
            For j As Integer = 0 To dt.Rows.Count - 1
                dt.Rows(j)("身份證號碼") = Left(dt.Rows(j)("身份證號碼").ToString(), 3) & New String("X", 3) & Right(dt.Rows(j)("身份證號碼").ToString(), 4)
                dt.Rows(j)("檢查結果") = dt.Rows(j)("檢查結果").ToString().TrimEnd(",")
            Next
            Me.gv.DataSource = dt
            Me.gv.DataBind()
            Return False
        Else

            Using scope As New Transactions.TransactionScope
                dt.Columns.Remove("檢查結果")
                For i = 0 To dt.Rows.Count - 1
                    '先將此月的該志工時數刪除
                    sql.Length = 0
                    sql.AppendFormat(" Delete From ImportWorkHourRecord Where VolunteerID='{0}' ", dt.Rows(i)("身份證號碼").ToString().Trim())
                    sql.AppendFormat(" And EffectiveDate='{0}' ", dt.Rows(i)("年度迄").ToString())
                    sql.AppendFormat(" And StartDate = '{0}'; ", dt.Rows(i)("年度起").ToString())


                    'If Me.cbNotWorkHour.Checked Then

                    'End If
                    '新增志工時數(環境清掃)
                    sql.AppendFormat(" Insert Into ImportWorkHourRecord(ItemType,VOrganizationID,VolunteerID,WorkHour,WorkTimes,StartDate,EffectiveDate,CreateDate,CreateBy,Active) Select ")
                    sql.AppendFormat(" '1' ")
                    sql.AppendFormat(",'{0}' ", "")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號碼").ToString().Trim())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("環境清掃").ToString().Trim())
                    sql.AppendFormat(",'1' ")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度起").ToString())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度迄").ToString())
                    sql.AppendFormat(",getDate() ")
                    sql.AppendFormat(",'{0}' ", clsAuth.GetUser().UserID)
                    sql.AppendFormat(",'Y'; ")

                    '新增志工時數(宣導)
                    sql.AppendFormat(" Insert Into ImportWorkHourRecord(ItemType,VOrganizationID,VolunteerID,WorkHour,WorkTimes,StartDate,EffectiveDate,CreateDate,CreateBy,Active) Select ")
                    sql.AppendFormat(" '2' ")
                    sql.AppendFormat(",'{0}' ", "")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號碼").ToString().Trim())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("宣導").ToString().Trim())
                    sql.AppendFormat(",'1' ")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度起").ToString())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度迄").ToString())
                    sql.AppendFormat(",getDate() ")
                    sql.AppendFormat(",'{0}' ", clsAuth.GetUser().UserID)
                    sql.AppendFormat(",'Y'; ")

                    '新增志工時數(導覽)
                    sql.AppendFormat(" Insert Into ImportWorkHourRecord(ItemType,VOrganizationID,VolunteerID,WorkHour,WorkTimes,StartDate,EffectiveDate,CreateDate,CreateBy,Active) Select ")
                    sql.AppendFormat(" '3' ")
                    sql.AppendFormat(",'{0}' ", "")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號碼").ToString().Trim())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("導覽").ToString().Trim())
                    sql.AppendFormat(",'1' ")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度起").ToString())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度迄").ToString())

                    sql.AppendFormat(",getDate() ")
                    sql.AppendFormat(",'{0}' ", clsAuth.GetUser().UserID)
                    sql.AppendFormat(",'Y'; ")

                    '新增志工時數(河川巡守)
                    sql.AppendFormat(" Insert Into ImportWorkHourRecord(ItemType,VOrganizationID,VolunteerID,WorkHour,WorkTimes,StartDate,EffectiveDate,CreateDate,CreateBy,Active) Select ")
                    sql.AppendFormat(" '4' ")
                    sql.AppendFormat(",'{0}' ", "")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號碼").ToString().Trim())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("河川巡守").ToString().Trim())
                    sql.AppendFormat(",'1' ")
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度起").ToString())
                    sql.AppendFormat(",'{0}' ", dt.Rows(i)("年度迄").ToString())

                    sql.AppendFormat(",getDate() ")
                    sql.AppendFormat(",'{0}' ", clsAuth.GetUser().UserID)
                    sql.AppendFormat(",'Y'; ")
                    'MyObj.WriteErrorLog(sql.ToString())
                    dat.AccData(sql)

                Next
                scope.Complete()
                Return True


            End Using

        End If
    End Function
    Private Function MonthString() As String
        Return "01,02,03,04,05,06,07,08,09,10,11,12"
    End Function
    Public Function GetVolunteerID(ByVal VolunteerID As String, ByVal VolunteerName As String) As String
        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        sql.Length = 0
        sql.AppendFormat("select  VolunteerID from Volunteer ")
        sql.AppendFormat("where left(VolunteerID,3)+REPLICATE('X',3)+right(VolunteerID,4) ='{0}' ", VolunteerID)
        sql.AppendFormat("AND ltrim(replace(replace(VolunteerName, '  ', ''), ' ', '')) = N'{0}' ", LTrim(VolunteerName.Replace("  ", "").Replace(" ", "")))
        '  MyObj.WriteLog(sql.ToString())
        If dat.GetData(sql).Rows.Count = 1 Then
            Return dat.GetData(sql).Rows(0)("VolunteerID").ToString()
        ElseIf dat.GetData(sql).Rows.Count > 1 Then
            Return "Error"
        Else
            Return ""
        End If
    End Function
    Private Function WorkHourRecord(ByVal WorkType As String, ByVal YYMM As String, ByVal YYMMEND As String, ByVal VolunteerID As String) As String
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        sql.Length = 0
        sql.AppendFormat("SELECT distinct whr.VolunteerID,SUM(whr.WorkHour) as  WorkHour FROM CleanPlan c ")
        sql.AppendFormat("INNER JOIN CleanPlanMapping cpm on c.PlanID=cpm.PlanID ")
        sql.AppendFormat("INNER JOIN WorkHeader wh on cpm.WorkHeaderID=wh.WorkHeaderID ")
        sql.AppendFormat("INNER JOIN WorkRecord wr on wh.WorkHeaderID=wr.WorkHeaderID ")
        sql.AppendFormat("INNER JOIN WorkHourRecord whr on wr.RecordID=whr.RecordID ")
        sql.AppendFormat("WHERE whr.Active='Y' ")
        sql.AppendFormat("AND (whr.EffectiveDate between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}') ", YYMM, YYMMEND)

        sql.AppendFormat("AND c.ItemType='{0}' ", WorkType)
        sql.AppendFormat("AND whr.VolunteerID='{0}' ", VolunteerID)
        sql.AppendFormat("GROUP BY  whr.VolunteerID ")

        If dat.GetData(sql).Rows.Count > 0 Then
            Return dat.GetData(sql).Rows(0)("WorkHour").ToString()
        Else
            Return ""
        End If
    End Function
    Private Sub Export2()


        Dim dt As Data.DataTable = Me.GetGVData()

        Dim ActiveDT As New DataTable
        dt.DefaultView.RowFilter = "Active ='Y' "
        ActiveDT = dt.DefaultView.ToTable()
        If dt.Rows.Count > 0 Then


            If Me.cbNotReserve.Checked = False Then

                dt = ActiveDT
            End If
            dt.Columns.Add("StartDate")
            dt.Columns.Add("EffectiveDate")
            dt.Columns.Add("TotalHour")
            dt.Columns.Add("ItemType1Hour")
            dt.Columns.Add("ItemType2Hour")
            dt.Columns.Add("ItemType3Hour")
            dt.Columns.Add("ItemType4Hour")

            dt.DefaultView.RowFilter = "VolunteerID is not null"
            dt.DefaultView.Sort = "Active desc"
            For i As Integer = 0 To dt.Rows.Count - 1
                dt.Rows(i)("VolunteerID") = Left(dt.Rows(i)("VolunteerID").ToString(), 3) & New String("X", 3) & Right(dt.Rows(i)("VolunteerID").ToString(), 4)
            Next

            For i As Integer = 0 To dt.Columns.Count - 1
                dt.Columns(i).ColumnName = ChangeColumnName(dt.Columns(i).ColumnName)
            Next

            dt = dt.DefaultView.ToTable()

            dt.Columns.Remove("Active")

            Dim ms As MemoryStream = TryCast(DataTableRenderToExcel.RenderDataTableToExcelSetSheet(dt, "ImportWorkRecordSample"), MemoryStream)


            ' 設定強制下載標頭。 
            Response.AddHeader("Content-Disposition", String.Format("attachment; filename=ImportWorkRecordSample.xls"))
            ' 輸出檔案。 
            Response.BinaryWrite(ms.ToArray())

            ms.Close()
            ms.Dispose()
        Else
            MyObj.ShowMessage("您選擇的資料類型沒有資料可供匯出!")
        End If
    End Sub
    Protected Function ChangeColumnName(ByVal ColumnName As String) As String
        Dim ChineseColumnName As String = ""
        Select Case ColumnName
            Case "VolunteerName"
                ChineseColumnName = "姓名" & Space(1)
            Case "VolunteerID"
                ChineseColumnName = "身份證號碼"
            Case "SerialNumber"
                ChineseColumnName = "紀錄冊號碼" & Space(5)
            Case "Birthday"
                ChineseColumnName = "生日" & Space(1)
            Case "StartDate"
                ChineseColumnName = "年度月份(起)"
            Case "EffectiveDate"
                ChineseColumnName = "年度月份(迄)"
            Case "TotalHour"
                ChineseColumnName = "總時數"
            Case "ItemType1Hour"
                ChineseColumnName = "環境清掃"
            Case "ItemType2Hour"
                ChineseColumnName = "宣導"
            Case "ItemType3Hour"
                ChineseColumnName = "導覽"
            Case "ItemType4Hour"
                ChineseColumnName = "河川巡守"
            Case "Active"
                ChineseColumnName = "Active"
        End Select
        Return ChineseColumnName
    End Function
    ' Public Event OrgIndexChange(ByVal source As Object, ByVal e As EventArgs)

    Public Property UOrganizationID() As String
        Get
            Return ViewState("UOrganizationID")
        End Get
        Set(ByVal value As String)
            ViewState("UOrganizationID") = value
        End Set
    End Property
End Class
